CREATE PROCEDURE sp_asi_ClientUpgrade AS
DECLARE     @machinename varchar(255),
        @MACaddress varchar(20),
        @installerpath varchar(255),
        @clientomnispath varchar(255),
        @clientimispath varchar(255),
        @usermessage varchar(2048),
        @cmd varchar(2048),
        @tmpnum int
--Set defaults for optional paths
SET @clientomnispath = ''
SET @clientimispath =''
SET @installerpath = ''
        
--Check to see if the InstallerPath exists
set @tmpnum = (select count(*)
             FROM System_Params
            WHERE ParameterName = 'System_Control.InstallerPath')
if @tmpnum = 0
    goto endit
--Since Omnis suppresses machine name, resolve its address from its MAC address
--Will not retrieve address of local host!!
SET @MACaddress = (SELECT lower(net_address)
            FROM master.dbo.sysprocesses
            WHERE spid = @@spid)
--Stuff in the hypens for a real address as arp uses
SET @MACaddress = STUFF(@MACaddress, 11, 0, '-')
SET @MACaddress = STUFF(@MACaddress, 9, 0, '-')
SET @MACaddress = STUFF(@MACaddress, 7, 0, '-')
SET @MACaddress = STUFF(@MACaddress, 5, 0, '-')
SET @MACaddress = STUFF(@MACaddress, 3, 0, '-')
    
--Get the ipaddress of the machine
drop table #Macaddresses    
create table #Macaddresses (CMD_OUTPUT varchar(300) null)
insert into #Macaddresses exec master.dbo.xp_cmdshell 'arp -A'
    
--Get the ip address of the client
SET @machinename = (SELECT ltrim(rtrim(substring(CMD_OUTPUT,1,20)))
            FROM #Macaddresses
            WHERE CMD_OUTPUT like '%' + @MACaddress + '%')
--Get push install params from database
--iMIS installer path
SET @installerpath = (SELECT ShortValue
            FROM System_Params
            WHERE ParameterName = 'System_Control.InstallerPath')
        
--Client paths
SET @clientomnispath = (SELECT rtrim(ShortValue)
            FROM System_Params
            WHERE ParameterName = 'System_Control.ClientOmnisPath')
SET @clientimispath = (SELECT rtrim(ShortValue)
            FROM System_Params
            WHERE ParameterName = 'System_Control.ClientiMISPath')
--Message for client
SET @usermessage = (SELECT rtrim(ShortValue)
            FROM System_Params
            WHERE ParameterName = 'System_Control.UserUpgradeMsg')
if datalength (@usermessage) = 0 or @usermessage IS NULL -- default message
    set @usermessage = 'Your system is beginning iMIS upgrade procedures.  Exit iMIS and any running applications.  When prompted, follow the instructions to upgrade your current installation.'
--If the Installer path is blank, DO NOT push
IF rtrim(@installerpath) = '' or datalength (@installerpath) = 0
    GOTO endit
IF @clientomnispath IS NULL
    SET @clientomnispath = ' '
IF @clientimispath IS NULL
    SET @clientimispath = ' '
--Check which optional parameters are actually set
IF  isnull(@clientomnispath,'') <> ''
    SET @clientomnispath = ' /o ' + @clientomnispath
IF  isnull(@clientimispath,'') <> ''
    SET @clientimispath = ' /i ' + @clientimispath
        
--Check the installer path ends with a \
IF right(@installerpath,1) <> '\'
    SET @installerpath = @installerpath + '\'
        
/*xCmd v1.0 for NT4/2000 - executes commands remotely
Freeware! 2001 Zoltan Csizmadia, zoltan_csizmadia@yahoo.com
        
Usage: xCmd.exe \\computer [options] command/exe arguments
        
Options:
   /D:directory           Set working directory
                          Default: Remote "%SystemRoot%\System32"
   /IDLE                  Idle priority class
   /NORMAL                Normal priority class
   /HIGH                  High priority class
   /REALTIME              Realtime priority class
   /C                     Copy the specified program to the remote mac
                          "%SystemRoot%\System32" directory
                          Commands's exe file must be absolute to loca
   /USER:user             User for remote connection
   /PWD:{password|*}      Password for remote connection
   /NOWAIT                Don't wait for remote process to terminate
Examples:
   xCmd.exe \\remote cmd       // Starts a "telnet" client
   xCmd.exe \\remote /nowait runme.exe
   xCmd.exe \\remote /user:administrator dir c:\
   xCmd.exe \\remote /user:somebody /pwd:* /d:d:\ test1.exe
   xCmd.exe \\remote /c /user:somebody /pwd:* /d:d:\ test2.exe
        
Notes:
- Input is passed to remote machine when you press the ENTER.
- Ctrl-C terminates the remote process
- Command and file path arguments have to be absolute to remote machin
  If you are using /c option, command exe file path must be absolute t
  local machine, but the arguments must be absolute to remote machine*/
    
--Display upgrade message to user
SET @cmd = 'net send ' + rtrim(@machinename) + '  "' + rtrim(@usermessage) + '"'
EXEC master.dbo.xp_cmdshell @cmd
--Launch installer
SET @cmd = 'xCmd \\' + rtrim(@machinename) + ' /C /NOWAIT '  + rtrim(@installerpath) + 'Setup.exe /z"/u' + rtrim(@clientomnispath) + rtrim(@clientimispath) + '"'
EXEC master.dbo.xp_cmdshell @cmd
endit:
RETURN

GO
GRANT EXECUTE ON  [dbo].[sp_asi_ClientUpgrade] TO [IMIS]
GO